﻿Imports Microsoft.Office.Interop
Public Class sanr2010

    Private Sub btn_replace_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_replace.Click
        Me.Label4.Text = "Ersetzen gestartet"

        Dim searchstrings As New ArrayList
        Dim replacestrings As New ArrayList
        Dim countervalue As Int64 = 1



        If rb_List.Checked = True Then
            Dim exl As New Excel.Application
            Dim wkb As Excel.Workbook = exl.Workbooks.Open(tb_Excel.Text.ToString())
            Dim wks As Excel.Worksheet = wkb.Worksheets(1)
            Dim valuesfound As Boolean = True
            Dim searchstring As String
            Dim replacestring As String
            Do While valuesfound = True

                If IsDBNull(wks.Cells(countervalue, 1).value) = False And wks.Cells(countervalue, 1).value <> "" Then
                    searchstring = wks.Cells(countervalue, 1).value.ToString()
                Else
                    searchstring = ""
                End If
                If IsDBNull(wks.Cells(countervalue, 2).value) = False And wks.Cells(countervalue, 2).value <> "" Then
                    replacestring = wks.Cells(countervalue, 2).value.ToString()
                Else
                    replacestring = ""
                End If

                If Len(searchstring) > 0 And Len(replacestring) > 0 Then
                    searchstrings.Add(searchstring)
                    replacestrings.Add(replacestring)
                    countervalue += 1
                Else
                    valuesfound = False
                End If
            Loop
            wkb.Close()
            exl.Quit()

        End If

        If rb_Single.Checked = True Then
            searchstrings.Insert(0, tb_suche.Text.ToString())
            replacestrings.Insert(0, tb_ersetzen.Text.ToString())
            replacestrings.TrimToSize()
        End If

        'Dim ex As New Excel.Application
        'Dim pp As New PowerPoint.Application
        Dim unterverz As FileIO.SearchOption

        If Me.cb_unterverzeichnisse.Checked = False Then
            unterverz = FileIO.SearchOption.SearchTopLevelOnly
        Else
            unterverz = FileIO.SearchOption.SearchAllSubDirectories
        End If

        If Me.cb_word.Checked = True Then
            Dim wo As New Word.Application
            Dim wod As Word.Document

            For Each foundFile As String In My.Computer.FileSystem.GetFiles(Me.tb_pfad.Text.ToString, unterverz, "*.doc*")
                'Dim findstring As String
                'Dim replacestring As String
                For countervalue = 0 To searchstrings.Capacity - 1

                    wod = wo.Documents.Open(foundFile)
                    'findstring = searchstrings(countervalue).ToString()
                    'replacestring = a
                    With wod.Content
                        .Select()
                        .Find.Execute2007(FindText:=searchstrings(countervalue).ToString(), Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll, ReplaceWith:=replacestrings(countervalue).ToString())

                    End With

                    wod.Close(Microsoft.Office.Interop.Word.WdSaveOptions.wdSaveChanges)
                Next
            Next
            wo.Quit()
        End If
        If Me.cb_powerpoint.Checked = True Then
            Dim pp As New PowerPoint.Application
            Dim ppd As PowerPoint.Presentation
            Dim sld As PowerPoint.Slide
            Dim shd As PowerPoint.Shape
            Dim oTxtRng As PowerPoint.TextRange
            Dim oTmpRng As PowerPoint.TextRange

            For Each foundFile As String In My.Computer.FileSystem.GetFiles(Me.tb_pfad.Text.ToString, unterverz, "*.ppt*")
                For countervalue = 0 To searchstrings.Capacity - 1
                    pp.Activate()
                    ppd = pp.Presentations.Open(FileName:=foundFile)
                    For Each sld In ppd.Slides
                        For Each shd In sld.Shapes
                            If shd.HasTextFrame = Microsoft.Office.Core.MsoTriState.msoTrue Then
                                oTxtRng = shd.TextFrame.TextRange
                                oTmpRng = oTxtRng.Replace(ReplaceWhat:=replacestrings(countervalue).ToString(), FindWhat:=searchstrings(countervalue).ToString(), WholeWords:=True)
                                Do While Not oTmpRng Is Nothing
                                    oTxtRng = oTxtRng.Characters(oTmpRng.Start + oTmpRng.Length, _
                                        oTxtRng.Length)
                                    oTmpRng = oTxtRng.Replace(ReplaceWhat:=replacestrings(countervalue).ToString(), FindWhat:=searchstrings(countervalue).ToString(), WholeWords:=True)
                                Loop

                            End If
                        Next
                    Next
                    ppd.Save()
                    ppd.Close()
                Next
            Next
            pp.Quit()

        End If
        If Me.cb_excel.Checked = True Then
            Dim exl As New Excel.Application
            Dim wkb As Excel.Workbook
            Dim wks As Excel.Worksheet
            Dim rag As Excel.Range

            For Each foundFile As String In My.Computer.FileSystem.GetFiles(Me.tb_pfad.Text.ToString, unterverz, "*.xls*")
                For countervalue = 0 To searchstrings.Capacity - 1
                    wkb = exl.Workbooks.Open(Filename:=foundFile)
                    For Each wks In wkb.Worksheets
                        wks.Activate()
                        rag = wks.Range("A1", "XFD1048576")
                        rag.Replace(What:=searchstrings(countervalue).ToString(), Replacement:=replacestrings(countervalue).ToString())
                    Next
                    If wkb.Saved = False Then
                        wkb.Save()
                    End If
                    wkb.Close()
                Next
            Next
            exl.Quit()
        End If
        Me.Label4.Text = "Ersetzen beendet"
    End Sub


    Private Sub frm_start_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Me.cb_excel.Visible = False
        'Me.cb_powerpoint.Visible = False
        Me.tb_pfad.Text = Application.ExecutablePath.ToString
    End Sub

    Private Sub SingleList_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rb_Single.CheckedChanged, rb_List.CheckedChanged
        If rb_Single.Checked = True Then
            tb_ersetzen.Enabled = True
            tb_suche.Enabled = True
            tb_Excel.Enabled = False
        Else
            tb_ersetzen.Enabled = False
            tb_suche.Enabled = False
            tb_Excel.Enabled = True
        End If
    End Sub
End Class
